﻿using System;
using System.Data;
using System.Configuration;
//using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
//using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;
using System.Globalization;

/// <summary>
/// Descripción breve de DB
/// </summary>
public class DB
{
	public DB()
	{
		//
		// TODO: Agregar aquí la lógica del constructor
		//
	}

    public static SqlConnection OpenConnection() {
        SqlConnection cnn = null;
        try
        {
            cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=BPOphotos;Integrated Security=True;Pooling=False");
            cnn.Open();
        }
        catch (Exception e) {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return cnn;
    }

    public static DataSet getStatus(string perfilid) 
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
         result.Tables["Tabla"].Columns.Add("Path");
        result.Tables["Tabla"].Columns.Add("estado_id");
        result.Tables["Tabla"].Columns.Add("Status");
        result.Tables["Tabla"].Columns.Add("Color");
       result.Tables["Tabla"].Columns.Add("Conteo");
        
        
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            if (perfilid.ToUpper().Equals("ADMIN"))
            {
                comm.CommandText = "SELECT  STATUS.path, STATUS.status_id, STATUS.name, STATUS.color FROM STATUS";
            }
            else {
                comm.CommandText = "SELECT  STATUS.path, STATUS.status_id, STATUS.name, STATUS.color FROM STATUS WHERE STATUS.name<>'paid'";
            }
            
            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read()) {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC4 = reader.GetString(0);//path
                if (!reader.IsDBNull(1)) rgb.intC1 = reader.GetInt32(1);//estadid
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//status
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//color
                rgb.strC5 = getStatusCount(rgb.intC1.ToString());//conteo


                object[] objArr = { rgb.strC4,rgb.intC1, rgb.strC2, rgb.strC3, rgb.strC5, };
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();
        }
        catch (Exception e) {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return result;
    }

    private static string getStatusCount(string statusid)
    {
         string result = "0";
         try
        {
             SqlConnection conn = null;
       
             conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;
           
            string strsql = "select count(order_id)as count from [ORDER] where status_id = " + statusid ;
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "state");
            dt = ds.Tables["state"];
            if (dt.Rows.Count == 0)
            {
                result = "0";
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = fila["count"].ToString();
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = "0";

        }
        return result;
    }
    // si tiene direccion donde recoger
    public static int hasOptional(string par){
     int result =0;

     
        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;
            
            string strsql = "select address from product where name='" + par+"'";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "product");
            dt = ds.Tables["product"];
            if (dt.Rows.Count == 0)
            {
                result = 0;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    if ( int.Parse(fila["address"].ToString())==1){
                       result=1;
                    }
                    else{
                        result=0;
                    }
                   

                }
            }
            conn.Close();
        }
        catch (Exception e) {
            Utility.writeError(e.StackTrace);
            result = -1;
        
        }
        return result;
    
    
    }

    public static int getNextOrderId() {

        int result = 0;


        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;
            
            string strsql = "select top 1 order_id from [ORDER] order by 1 desc";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "order");
            dt = ds.Tables["order"];
            if (dt.Rows.Count == 0)
            {
                result = 1;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila["order_id"].ToString()) +1 ;
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = -1;

        }
        return result;
    
    }

    public static RE_GenericBean getOrden(int par) {

        
        RE_GenericBean rgb = new RE_GenericBean();
        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "SELECT     [ORDER].ordernum, PRODUCT.name, [ORDER].client_id, convert(varchar, [ORDER].duedate,101), [ORDER].duetime, [ORDER].comments, [ORDER].stnum, [ORDER].stadd,[ORDER].stcity, STATE.name AS Expr2, [ORDER].zip, STATUS.name AS Expr3, [ORDER].rcvdate, [ORDER].rcvtime, [ORDER].opstnum,[ORDER].opstadd, [ORDER].opcity, [ORDER].opstate, [ORDER].opzip, PRODUCT.address, COUNTY.name AS Expr1, [ORDER].fee, PRODUCT.vendor_fee, [ORDER].account, [ORDER].company, [ORDER].specialatttributes FROM [ORDER] INNER JOIN PRODUCT ON [ORDER].product_id = PRODUCT.product_id INNER JOIN STATE ON [ORDER].state = STATE.state_id INNER JOIN STATUS ON [ORDER].status_id = STATUS.status_id INNER JOIN COUNTY ON [ORDER].county = COUNTY.county_id AND STATE.state_id = COUNTY.state_id WHERE     ([ORDER].order_id = " + par + ")";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "order");
            dt = ds.Tables["order"];
            if (dt.Rows.Count == 0)
            {
                return rgb;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    if (fila[0]!=null) rgb.intC1 = int.Parse(fila[0].ToString());  //orden_#
                    if (fila[1] != null) rgb.strC1 = fila[1].ToString(); //product_id name
                    if (fila[2] != null) rgb.strC22 = (fila[2].ToString()); //client_id
                    if (fila[3] != null) rgb.strC2 = fila[3].ToString(); //duedate
                    if (fila[4] != null) rgb.strC3 = fila[4].ToString(); //duetime
                    if (fila[5] != null) rgb.strC4 = fila[5].ToString();//comments
                    if (fila[6] != null) rgb.strC5 = fila[6].ToString();//stnum
                    if (fila[7] != null) rgb.strC6 = fila[7].ToString();//stadd
                    if (fila[8] != null) rgb.strC7 = fila[8].ToString();//stcity
                    if (fila[9] != null) rgb.strC8 = fila[9].ToString();//state
                    if (fila[10] != null) rgb.strC9 = fila[10].ToString();//zip
                    if (fila[11] != null) rgb.strC10 = fila[11].ToString();//status
                    if (fila[12] != null) rgb.strC11 = fila[12].ToString();//rcvdate
                    if (fila[13] != null) rgb.strC12 = fila[13].ToString();//rcvtime
                    if (fila[14] != null) rgb.strC13 = fila[14].ToString();//opstnum
                    if (fila[15] != null) rgb.strC14 = fila[15].ToString();//opstadd
                    if (fila[16] != null) rgb.strC15 = fila[16].ToString();//opcity
                    if (fila[17] != null) rgb.strC16 = fila[17].ToString();//opstate
                    if (fila[18] != null) rgb.strC17 = fila[18].ToString();//opzip
                    if (fila[19] != null) rgb.intC4 = int.Parse(fila[19].ToString());//product.address if have opt
                    if (fila[20] != null) rgb.strC18 = fila[20].ToString();//county
                    if (fila[21] != null) rgb.strC20 = fila[21].ToString(); //TotalFee-Monto
                    if (fila[22] != null) rgb.strC21 = fila[22].ToString(); //vendor_fee
                    if (fila[23] != null) rgb.strC22 = fila[23].ToString(); //account
                    if (fila[24] != null) rgb.strC23 = fila[24].ToString(); //company
                    if (fila[25] != null) rgb.strC24 = fila[25].ToString(); //specialAttributes

                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            return null;

        }


        return rgb;
    
    
    }

    public static DataSet GetOrders(string permiso, string filterstatus)
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("Order id");//2
        result.Tables["Tabla"].Columns.Add("Order #");//3
        result.Tables["Tabla"].Columns.Add("Due Date");//4
        result.Tables["Tabla"].Columns.Add("Due Time");//5
        result.Tables["Tabla"].Columns.Add("Client");// 6client
        result.Tables["Tabla"].Columns.Add("Company");//7 client
        result.Tables["Tabla"].Columns.Add("Account");//8 client
        result.Tables["Tabla"].Columns.Add("Type");//9
        result.Tables["Tabla"].Columns.Add("Street No.");//10
        result.Tables["Tabla"].Columns.Add("Street Address");//11
        result.Tables["Tabla"].Columns.Add("City");//12
        result.Tables["Tabla"].Columns.Add("State");//13
        result.Tables["Tabla"].Columns.Add("Zip");//14
        result.Tables["Tabla"].Columns.Add("County");//15
        result.Tables["Tabla"].Columns.Add("Procesor");//16
        result.Tables["Tabla"].Columns.Add("Driver ");//17
        result.Tables["Tabla"].Columns.Add("PI Comments");//18
        result.Tables["Tabla"].Columns.Add("Status ");//19
        result.Tables["Tabla"].Columns.Add("Appt Date");//20
        result.Tables["Tabla"].Columns.Add("Appt Comments");//21
       
        result.Tables["Tabla"].Columns.Add("specialatttributes");//22 special
       


        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            long testpermiso = (long.Parse(permiso) & 8);
             if (!filterstatus.Equals("all"))
                {

                    if (filterstatus.ToUpper().Equals("DEFAULT"))
                    {
                        if (testpermiso == 8)
                        {
                            comm.CommandText = "SELECT [ORDER].order_id, [ORDER].ordernum, CONVERT(varchar, [ORDER].duedate, 101) , [ORDER].duetime, [ORDER].client_id, [ORDER].company, [ORDER].account, upper(PRODUCT.initial), upper([ORDER].stnum), upper([ORDER].stadd), upper([ORDER].stcity), upper(STATE.initial), [ORDER].zip, upper(COUNTY.initial) AS  county, [ORDER].processor_status, [ORDER].processor_user,  [ORDER].comments, upper(STATUS.name), [ORDER].appdate, upper([ORDER].appcomments), [ORDER].specialatttributes FROM  [ORDER] INNER JOIN COUNTY ON [ORDER].county = COUNTY.county_id INNER JOIN  STATE ON [ORDER].state = STATE.state_id AND COUNTY.state_id = STATE.state_id INNER JOIN STATUS ON [ORDER].status_id = STATUS.status_id INNER JOIN PRODUCT ON [ORDER].product_id = PRODUCT.product_id WHERE Upper(STATUS.name)='PENDING' or Upper(STATUS.name)='ASSIGN' ORDER BY 3 ASC, 4 DESC";
                        }else{
                            comm.CommandText = "[ORDER].order_id, [ORDER].ordernum, CONVERT(varchar, [ORDER].duedate, 101) , [ORDER].duetime, [ORDER].client_id, [ORDER].company, [ORDER].account, upper(PRODUCT.initial), upper([ORDER].stnum), upper([ORDER].stadd), upper([ORDER].stcity), upper(STATE.initial), [ORDER].zip, upper(COUNTY.initial) AS  county, [ORDER].processor_status, [ORDER].processor_user,  [ORDER].comments, upper(STATUS.name), [ORDER].appdate, upper([ORDER].appcomments), [ORDER].specialatttributes FROM  [ORDER] INNER JOIN COUNTY ON [ORDER].county = COUNTY.county_id INNER JOIN  STATE ON [ORDER].state = STATE.state_id AND COUNTY.state_id = STATE.state_id INNER JOIN STATUS ON [ORDER].status_id = STATUS.status_id INNER JOIN PRODUCT ON [ORDER].product_id = PRODUCT.product_id WHERE Upper(STATUS.name)='PENDING' or Upper(STATUS.name)='ASSIGN' and Upper(STATUS.name) <>'PAID' ORDER BY 3 ASC, 4 DESC";  
                        }

                    }
                    else
                    {
                        if (testpermiso == 8)
                        {
                            comm.CommandText = "[ORDER].order_id, [ORDER].ordernum, CONVERT(varchar, [ORDER].duedate, 101) , [ORDER].duetime, [ORDER].client_id, [ORDER].company, [ORDER].account, upper(PRODUCT.initial), upper([ORDER].stnum), upper([ORDER].stadd), upper([ORDER].stcity), upper(STATE.initial), [ORDER].zip, upper(COUNTY.initial) AS  county, [ORDER].processor_status, [ORDER].processor_user,  [ORDER].comments, upper(STATUS.name), [ORDER].appdate, upper([ORDER].appcomments), [ORDER].specialatttributes FROM  [ORDER] INNER JOIN COUNTY ON [ORDER].county = COUNTY.county_id INNER JOIN  STATE ON [ORDER].state = STATE.state_id AND COUNTY.state_id = STATE.state_id INNER JOIN STATUS ON [ORDER].status_id = STATUS.status_id INNER JOIN PRODUCT ON [ORDER].product_id = PRODUCT.product_id WHERE Upper(STATUS.name)='" + filterstatus + "'ORDER BY 3 ASC, 4 DESC";
                        }
                        else
                        {
                            if (filterstatus.ToUpper().Equals("PAID"))
                            {
                                comm.CommandText = "[ORDER].order_id, [ORDER].ordernum, CONVERT(varchar, [ORDER].duedate, 101) , [ORDER].duetime, [ORDER].client_id, [ORDER].company, [ORDER].account, upper(PRODUCT.initial), upper([ORDER].stnum), upper([ORDER].stadd), upper([ORDER].stcity), upper(STATE.initial), [ORDER].zip, upper(COUNTY.initial) AS  county, [ORDER].processor_status, [ORDER].processor_user,  [ORDER].comments, upper(STATUS.name), [ORDER].appdate, upper([ORDER].appcomments), [ORDER].specialatttributes FROM  [ORDER] INNER JOIN COUNTY ON [ORDER].county = COUNTY.county_id INNER JOIN  STATE ON [ORDER].state = STATE.state_id AND COUNTY.state_id = STATE.state_id INNER JOIN STATUS ON [ORDER].status_id = STATUS.status_id INNER JOIN PRODUCT ON [ORDER].product_id = PRODUCT.product_id WHERE STATUS.name='" + filterstatus + "'ORDER BY 3 ASC, 4 DESC";
                            }
                            else
                            {
                                comm.CommandText = "SELECT [ORDER].order_id, [ORDER].ordernum, CONVERT(varchar, [ORDER].duedate, 101) , [ORDER].duetime, [ORDER].client_id, [ORDER].company, [ORDER].account, upper(PRODUCT.initial), upper([ORDER].stnum), upper([ORDER].stadd), upper([ORDER].stcity), upper(STATE.initial), [ORDER].zip, upper(COUNTY.initial) AS  county, [ORDER].processor_status, [ORDER].processor_user,  [ORDER].comments, upper(STATUS.name), [ORDER].appdate, upper([ORDER].appcomments), [ORDER].specialatttributes FROM  [ORDER] INNER JOIN COUNTY ON [ORDER].county = COUNTY.county_id INNER JOIN  STATE ON [ORDER].state = STATE.state_id AND COUNTY.state_id = STATE.state_id INNER JOIN STATUS ON [ORDER].status_id = STATUS.status_id INNER JOIN PRODUCT ON [ORDER].product_id = PRODUCT.product_id WHERE STATUS.name='" + filterstatus + "' and Upper(STATUS.name) <>'PAID' ORDER BY 3 ASC, 4 DESC";

                            }
                        }
                    }
                    
                }
                else {

                    if (testpermiso == 8)
                    {
                        comm.CommandText = "SELECT [ORDER].order_id, [ORDER].ordernum, CONVERT(varchar, [ORDER].duedate, 101), [ORDER].duetime,[ORDER].client_id, [ORDER].company, [ORDER].account, upper(PRODUCT.initial), upper([ORDER].stnum), upper([ORDER].stadd), upper([ORDER].stcity), upper(STATE.initial), [ORDER].zip, upper(COUNTY.initial) AS  county, [ORDER].processor_status, [ORDER].processor_user , [ORDER].comments, upper(STATUS.name), upper(STATUS.name), [ORDER].appdate, upper([ORDER].].appcomments),[ORDER].specialatttributes FROM  [ORDER] INNER JOIN COUNTY ON [ORDER].county = COUNTY.county_id INNER JOIN  STATE ON [ORDER].state = STATE.state_id AND COUNTY.state_id = STATE.state_id INNER JOIN STATUS ON [ORDER].status_id = STATUS.status_id INNER JOIN  PRODUCT ON [ORDER].product_id = PRODUCT.product_id WHERE UPPER(STATUS.name)<>'COMPLETED' ORDER BY 3 ASC, 4 DESC";
                    }
                    else {
                        comm.CommandText = "SELECT [ORDER].order_id, [ORDER].ordernum, CONVERT(varchar, [ORDER].duedate, 101), [ORDER].duetime, [ORDER].client_id, [ORDER].company, [ORDER].account,upper(PRODUCT.initial), upper([ORDER].stnum), upper([ORDER].stadd), upper([ORDER].stcity), upper(STATE.initial), [ORDER].zip, upper(COUNTY.initial) AS  county, [ORDER].processor_status, [ORDER].processor_user , [ORDER].comments, upper(STATUS.name), upper(STATUS.name), [ORDER].appdate, upper([ORDER].].appcomments),[ORDER].specialatttributes FROM  [ORDER] INNER JOIN COUNTY ON [ORDER].county = COUNTY.county_id INNER JOIN  STATE ON [ORDER].state = STATE.state_id AND COUNTY.state_id = STATE.state_id INNER JOIN STATUS ON [ORDER].status_id = STATUS.status_id INNER JOIN  PRODUCT ON [ORDER].product_id = PRODUCT.product_id WHERE UPPER(STATUS.name)<>'PAID' and UPPER(STATUS.name)<>'COMPLETED'  ORDER BY 3 ASC, 4 DESC";
                    }
                    
                
                }



             Utility.writeError(comm.CommandText);
            
            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //ordenid
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1); //orden#
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//due date
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3).ToString();//duetime
                if (!reader.IsDBNull(4)) rgb.strC3 = reader.GetString(4).ToString();//client
                if (!reader.IsDBNull(5)) rgb.strC4 = reader.GetInt32(5).ToString();//company
                if (!reader.IsDBNull(6)) rgb.strC5 = reader.GetInt32(6).ToString();//account
                if (!reader.IsDBNull(7)) rgb.strC6 = reader.GetString(7);//product name
                if (!reader.IsDBNull(8)) rgb.strC7 = reader.GetString(8);//st num
                if (!reader.IsDBNull(9)) rgb.strC8 = reader.GetString(9);//st address
                if (!reader.IsDBNull(10)) rgb.strC9 = reader.GetString(10);//city
                if (!reader.IsDBNull(11)) rgb.strC10 = reader.GetString(11);//state
                if (!reader.IsDBNull(12)) rgb.strC11 = reader.GetString(12);//zip
                if (!reader.IsDBNull(13)) rgb.strC12 = reader.GetString(13);//county
                if (!reader.IsDBNull(14)) rgb.strC13 = reader.GetString(14);//procesor status
                if (!reader.IsDBNull(15)) rgb.strC14 = reader.GetString(15);//processor user
                if (!reader.IsDBNull(16)) rgb.strC15 = reader.GetString(16);//pi comments
                if (!reader.IsDBNull(17)) rgb.strC16 = reader.GetString(17);//status
                if (!reader.IsDBNull(18)) rgb.strC17 = reader.GetString(18);//appt date
                if (!reader.IsDBNull(19)) rgb.strC18 = reader.GetString(19);//appt comments
               
                if (!reader.IsDBNull(20)) rgb.strC19 = reader.GetString(20);//special attributes




                object[] objArr = { rgb.intC1, rgb.intC2, rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4, rgb.strC5, rgb.strC6, rgb.strC7, rgb.strC8, rgb.strC9, rgb.strC10, rgb.strC11, rgb.strC12, rgb.strC13, rgb.strC14, rgb.strC15, rgb.strC16, rgb.strC17, rgb.strC18, rgb.strC19 };
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();

        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        //////////////////////
        return result;

    
    }


    public static DataSet GetProducts()
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("Product_id");//1
        result.Tables["Tabla"].Columns.Add("Name");//2
        result.Tables["Tabla"].Columns.Add("Initial");//3
        result.Tables["Tabla"].Columns.Add("Desription");//4
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            
                comm.CommandText = "SELECT  product_id, name, initial,descriptionagent from product";

            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //ordenid
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//due date
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//time
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//nombre producto
               
                object[] objArr = { rgb.intC1, rgb.strC1, rgb.strC2, rgb.intC4};
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();

        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return result;

    }
    public static DataSet GetStates()
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("state_id");//1
        result.Tables["Tabla"].Columns.Add("name");//2
        result.Tables["Tabla"].Columns.Add("initial");//3
        result.Tables["Tabla"].Columns.Add("enable");//3
        
      
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT  state_id, Upper(name) AS name, initial, enable from state order by 2 asc";

            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //state_id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//ma,e
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//initial
                if (!reader.IsDBNull(3)) rgb.intC2 = reader.GetInt32(3); //state_id
                object[] objArr = { rgb.intC1, rgb.strC1, rgb.strC2, rgb.intC2};
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();
        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return result;

    }

    public static DataSet GetCounties(string state)
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("county");//1
        result.Tables["Tabla"].Columns.Add("name");//2
        result.Tables["Tabla"].Columns.Add("enable");//3
        result.Tables["Tabla"].Columns.Add("initial");//4
        

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT c.county_id, c.name, c.enable, c.initial FROM COUNTY AS c INNER JOIN  STATE AS s ON c.state_id = s.state_id WHERE     (s.name = '"+state.ToString()+"') order by 2";

            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //county_id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//name
                if (!reader.IsDBNull(2)) rgb.intC2 = reader.GetInt32(2); //enable
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//initial
            


                object[] objArr = { rgb.intC1, rgb.strC1, rgb.intC2,rgb.strC2 };
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();

        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return result;

    }

    public static int getProductId(string nameproduct)
    {//obttiene Product_id por el nombre del produto

        int result = 0;

        try
        
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;
            
            string strsql = "select product_id from product where name = '"+nameproduct+"'";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "product");
            dt = ds.Tables["product"];
            if (dt.Rows.Count == 0)
            {
                result = 1;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila["product_id"].ToString()) ;
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = -1;

        }
        return result;

    }
    public static int getProductIdbyInitial(string initialproduct)
    {

        int result = 0;

        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "select product_id from product where initial = '" + initialproduct + "'";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "product");
            dt = ds.Tables["product"];
            if (dt.Rows.Count == 0)
            {
                result = 1;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila["product_id"].ToString());
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = -1;

        }
        return result;

    }

    public static int getCountyId(string namecounty)
    {

        int result = 0;


        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;
            
            string strsql = "select county_id from county where name = '" + namecounty + "'";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "county");
            dt = ds.Tables["county"];
            if (dt.Rows.Count == 0)
            {
                result = 1;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila["county_id"].ToString());
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = -1;

        }
        return result;

    }

    public static int getStateId(string namestate)
    {

        int result = 0;


        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;
            
            string strsql = "select state_id from state where name = '" + namestate + "'";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "state");
            dt = ds.Tables["state"];
            if (dt.Rows.Count == 0)
            {
                result = 1;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila["state_id"].ToString());
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = -1;

        }
        return result;

    }
    public static int isStateEnable(string state) {
        int result = 0;
        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;
            
            string strsql = "select enable from state where name = '" + state + "'";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "state");
            dt = ds.Tables["state"];
            if (dt.Rows.Count == 0)
            {
                result = 1;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila["state_id"].ToString());
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = -1;

        }
        return result;    
    
    }
    public static int hasFail(int orderid) {
        int result = 0;


        try
        {

            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;
            
            string strsql = "SELECT COUNT(order_id)conteo FROM   ORDER_FAIL WHERE order_id = " + orderid ;
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "tabla");
            dt = ds.Tables["tabla"];
            if (dt.Rows.Count == 0)
            {
                result = 0;
            }
            else
            {
                result = 1;
             }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError("DB has Fail"+e.StackTrace);

        }
        return result;
        
    
    }
    
    public static DataSet getFail(int orderid)
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("fail");//1
        result.Tables["Tabla"].Columns.Add("date");//2
        result.Tables["Tabla"].Columns.Add("agent");//3
        result.Tables["Tabla"].Columns.Add("responsable");//3
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT  FAIL.description, date,agent, responsable FROM   ORDER_FAIL INNER JOIN FAIL ON ORDER_FAIL.fail_id = FAIL.fail_id   WHERE order_id= "+orderid+" order by date desc";
            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0); //fail
                if (!reader.IsDBNull(1)) rgb.strC2 = reader.GetString(1);//date
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetString(2); //agent
                if (!reader.IsDBNull(3)) rgb.strC4 = reader.GetString(3); //responsable
                object[] objArr = { rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4};
                result.Tables["Tabla"].Rows.Add(objArr);
            }

        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        conn.Close();
        return result;


    }
    
    public static DataSet getProcess(int product) {
        //devuelve los procesos de catalogo de un producto

        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("name");//Item
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT PROCESS.name FROM  PRODUCT INNER JOIN PRODUCTS_PROCESS ON PRODUCT.product_id = PRODUCTS_PROCESS.product_id INNER JOIN PROCESS ON PRODUCTS_PROCESS.process_id = PROCESS.process_id WHERE  (PRODUCT.product_id = " + product + ") AND (PROCESS.internal <> 1)";
            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);//
                object[] objArr = { rgb.strC1 };
                result.Tables["Tabla"].Rows.Add(objArr);
            }


            conn.Close();


        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return result;
    }

   
    public static DataSet getProcessProduct(int product)
    {//devuelve los procesos ya asignados a un producto, quien los puede realizar

        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        
        
        result.Tables["Tabla"].Columns.Add("PRODUCT");//product
        result.Tables["Tabla"].Columns.Add("VENDOR");//vendor
        result.Tables["Tabla"].Columns.Add("FEES");//
        result.Tables["Tabla"].Columns.Add("DONE");//
        result.Tables["Tabla"].Columns.Add("DATE");//Item
        
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT PROCESS.name, PROCESS.perfil FROM  PRODUCT INNER JOIN PRODUCTS_PROCESS ON PRODUCT.product_id = PRODUCTS_PROCESS.product_id INNER JOIN PROCESS ON PRODUCTS_PROCESS.process_id = PROCESS.process_id WHERE  (PRODUCT.product_id = " + product + ") order by orden";
            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);//name
                if (!reader.IsDBNull(1)) rgb.intC1 = reader.GetInt32(1);//perfil de quien puede hacer ese proceso
                string srespuesta;
                
                //xml para obtener listado usuarios que pueden hacer esa acticidad
                // Service se= new Service();
                // srespuesta =   se.UsuariosporPerfil(int);
                //construyendo mi propia respuesta
                srespuesta =  "<?xml version=\"1.0\" encoding=\"utf-8\"?>";
                srespuesta = srespuesta + "<lista_usuarios><respuestaID>1</respuestaID>";
                srespuesta = srespuesta + "<datos_registro><user></user><name></name><lastname></lastname><comp-name></comp-name><alias></alias><email></email></datos_registro>";
                srespuesta = srespuesta + "/lista_usuarios";

                object[] objArr = { rgb.strC1, rgb.intC1 };
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();




        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return result;
    }

    public static int getPerfilProcess(string process) {
        int result = 0;
         SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT PROCESS.name, PROCESS.perfil FROM  PRODUCT INNER JOIN PRODUCTS_PROCESS ON PRODUCT.product_id = PRODUCTS_PROCESS.product_id INNER JOIN PROCESS ON PRODUCTS_PROCESS.process_id = PROCESS.process_id WHERE  (PRODUCT.product_id = " + process + ")";
            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(0);//perfil
            }
            conn.Close();
        }catch(Exception e){
        
        
        }

        return result;
       
    }

    public static RE_GenericBean getValueCampo(string campo, int order, string process) {
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT TOP (1)  ORDER_PROCESS.value, ORDER_PROCESS.date  FROM  CAMPOS INNER JOIN PROCESS ON CAMPOS.process_id = PROCESS.process_id INNER JOIN ORDER_PROCESS ON CAMPOS.campo_id = ORDER_PROCESS.campo_id WHERE  (CAMPOS.name = '" + campo + "') AND (ORDER_PROCESS.order_id = " + order + ") AND (PROCESS.name = '" + process + "') ORDER BY ORDER_PROCESS.id DESC";
            reader = comm.ExecuteReader();
           
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);//value
                if (!reader.IsDBNull(1)) rgb.strC2 = reader.GetString(1);//date
            }
            conn.Close();
        }
        catch (Exception e)
        {


        }


        return rgb;
    
    }

    public static int addFail(int orderid, int failid, string agent,  string responsable)
    {
        int result=0;
        try
        {
            SqlConnection conn = null;
            conn = DB.OpenConnection();
            string now = DateTime.Now.ToString();
            string q = "insert into ORDER_FAIL (order_id, fail_id, date, agent, responsable) values ("+orderid+", "+ failid+" , '"+now+"','"+agent+"', '"+responsable+"')";
            SqlCommand insert = new SqlCommand(q, conn);
            insert.ExecuteNonQuery();
            result = 1;
            conn.Close();
        }
        catch(Exception e){
            result = 0;
            Utility.writeError(e.StackTrace);
        }
        return result;
    
    }
    //DB.updateOrder(orden,p torder.p_2 Text,jobtype.Text,p_3cbstatus.Text, p_4company.Text, p_5account.Text, p_6 torderdate.Text, p_7 torderdate.Text, p_8 tduedate.Text, p_9 tduetime.Text, p_10 tstnumber.Text, p_11 tstaddress.Text, p_12 tcity.Text, p_13 county.Text, p_14 state.Text, p_15 tzip.Text , p_16 comments.Text);
    public static int updateOrder(int orden, string p, string p_2, string p_3, string p_4, string p_5, string p_6, string p_7, string p_8, string p_9, string p_10, string p_11, string p_12, string p_13, string p_14, string p_15, string p_16)
    {
        int result = 0;
        try
        {
            int productid = DB.getProductId(p_2);
            int statusid = DB.getStatusId(p_3);
            CultureInfo ci = new CultureInfo("en-US");
            DateTime duedate = DateTime.Parse(p_8);
            string miduedate =  duedate.ToString("d", ci);
            DateTime rcvdate = DateTime.Parse(p_6);
            string mircvdate = rcvdate.ToString("d", ci);
            SqlConnection conn = null;
            conn = DB.OpenConnection();
            string now = DateTime.Now.ToString();
            string q = "update [ORDER] set ordernum=" + p + ", product_id=" + productid + ", status_id=" + statusid + ", company=1, account=1, rcvdate ='" + rcvdate + "', rcvtime= '" + p_7 + "', duedate= '" + miduedate + "', duetime= '" + p_9 + "', stnum= '" + p_10 + "', stadd='" + p_11 + "', stcity= '" + p_12 + "', county="+getCountyId(p_13)+", state="+getStateId(p_14)+", zip='"+p_15+"', comments='"+p_16+"' WHERE order_id="+orden;
            SqlCommand insert = new SqlCommand(q, conn);
            insert.ExecuteNonQuery();
            conn.Close();
            result = 1;
        }
        catch (Exception e)
        {
            result = 0;
            Utility.writeError(e.StackTrace);
        }
        return result;
    }

    public static int getStatusId(string p_3)
    {
        int result = 0;

        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "select status_id from status where UPPER(name) = '" + p_3.ToUpper() + "'";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "product");
            dt = ds.Tables["product"];
            conn.Close();
            if (dt.Rows.Count == 0)
            {
                result = 1;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila["status_id"].ToString());
                }
            }
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = -1;

        }
        return result;
    }
    // DB.updateOrderAttibutes(p attributes.Text, p_2 appshe.Text,p_3 tfee.Text,p_4 tcheck.Text, p_5 paid.Text, p_6 asisValue.Text, p_7 repair.Text, p_8 efectivedate.Text );
    public static int updateOrderAttibutes(int orden,string p, string p_2, string p_3, string p_4, string p_5, string p_6, string p_7, string p_8)
    {
        int result = 0;
        try
        {
           
            CultureInfo ci = new CultureInfo("en-US");
            if (p_8=="") {
                p_8 = "";//validar que no se pueda registrar si no lleva esta fecha
            }
            
          
            SqlConnection conn = null;
            conn = DB.OpenConnection();
            if (p.ToUpper().Equals("YES")) { p = "YES"; } else { p = ""; }
            string q = "update [ORDER] set specialatttributes= '" + p + "', appscheduled='" + p_2 + "' , fee=" + p_3 + ", check# ='" + p_4 + "', paid='" + p_5 + "', asisvalue='" + p_6 + "', repairedvalue='" + p_7 + "',  efectivedate= '"+p_8+"' WHERE order_id=" + orden;
            SqlCommand insert = new SqlCommand(q, conn);
            insert.ExecuteNonQuery();
            result = 1;
        }
        catch (Exception e)
        {
            result = 0;
            Utility.writeError(e.StackTrace+ e.Message);
        }
        return result;
    }
    
    // DB.saveOrderProcess(processname, campo, value);
    public static int saveOrderProcess(int order, int campoid, string value)
    {
        int result=0;
         
        try
        {
           
            SqlConnection conn = null;
            SqlConnection conn2 = null;
            conn = OpenConnection();
            conn2 = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "SELECT TOP(1) ORDER_PROCESS.id, ORDER_PROCESS.order_id, ORDER_PROCESS.value   FROM   ORDER_PROCESS  WHERE    order_id = " + order + " AND  campo_id =" + campoid+ " ORDER BY id DESC ";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "CAMPOS");
            dt = ds.Tables["CAMPOS"];
            if (dt.Rows.Count == 0) //save
            {
               
                string q = "insert into ORDER_PROCESS (order_id, campo_id, value, date) values (" + order + ", " + campoid + " , '" + value + "','" +  DateTime.Now.ToString()+ "')";
                SqlCommand insert = new SqlCommand(q, conn2);
                insert.ExecuteNonQuery();
                result = 1;
                
            }
            else//update
            {
                int id = 0;
                string strvalue = "";
                foreach (DataRow fila in dt.Rows)
                {
                    id = int.Parse(fila[0].ToString());
                    strvalue = fila[2].ToString();
                }
                if  (!strvalue.ToUpper().Equals(value.ToUpper())){
                string u = "update  ORDER_PROCESS  set  value='"+ value+"', date='"+DateTime.Now.ToString()+"' WHERE id="+id;
                SqlCommand update = new SqlCommand(u, conn);
                update.ExecuteNonQuery();
                result = 1;
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = 0;

        }
         

        return result;
        
    }

  
    public static int getCampoID(string processname, string campo)
    {
        int result = 0;
        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "SELECT CAMPOS.campo_id  FROM   CAMPOS INNER JOIN  PROCESS ON CAMPOS.process_id = PROCESS.process_id WHERE     (UPPER(PROCESS.name) = UPPER('"+processname+"')) AND (UPPER(CAMPOS.name) = UPPER('"+campo+"'))";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "CAMPOS");
            dt = ds.Tables["CAMPOS"];
            if (dt.Rows.Count == 0)
            {
                result = 1;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila["campo_id"].ToString());
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = -1;

        }

     
        return result;
    }

    public static DataSet getDoneProduct(int order, int intCampo)
    {
        DataSet ds = new DataSet();
        try{
        SqlConnection conn = null;
        conn = OpenConnection();
        
        DataTable dt = new DataTable();
        SqlDataAdapter sqladapt;


        string strsql = "SELECT ORDER_PROCESS.id, ORDER_PROCESS.order_id, ORDER_PROCESS.value   FROM   ORDER_PROCESS  WHERE    order_id = " + order + " AND  campo_id =" + intCampo;
        sqladapt = new SqlDataAdapter(strsql, conn);
        sqladapt.Fill(ds, "CAMPOS");
        dt = ds.Tables["CAMPOS"];
        if (ds.Tables[0].Rows.Count == 0) { ds = null; }
        conn.Close();
        }catch(Exception e){
            Utility.writeError(e.StackTrace);
        }
       
        return ds;
    }

    public static void setStatusOrder(int order, int statusid)
    {
        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            string strsql = "UPDATE [ORDER] set status_id=" + statusid + " WHERE    order_id = " + order;
            SqlCommand update = new SqlCommand(strsql, conn);
            update.ExecuteNonQuery();
            conn.Close();
        }catch(Exception e){}
       
    }


    
    public static string  getProductType(string productname){
        string result="none";
        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "SELECT type  FROM   PRODUCT  WHERE     (UPPER(name) = '" + productname.ToUpper() +"')";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "CAMPOS");
            dt = ds.Tables["CAMPOS"];
            
            if (dt.Rows.Count == 0)
            {
                result = "none";
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = fila[0].ToString();
                }
                
            }
            conn.Close();
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = "none";

        }


        
        return result;
    }

    public static DataSet getProductView(int product_id)
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("name");
        result.Tables["Tabla"].Columns.Add("view_id");
        result.Tables["Tabla"].Columns.Add("num");
        result.Tables["Tabla"].Columns.Add("mandatory");
       

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT  [VIEW].name, [VIEW].view_id, PRODUCT_VIEW.num, PRODUCT_VIEW.mandatory FROM  PRODUCT_VIEW INNER JOIN  [VIEW] ON PRODUCT_VIEW.view_id = [VIEW].view_id WHERE        (PRODUCT_VIEW.product_id = "+product_id+") ORDER BY PRODUCT_VIEW.num";
            
           reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);//name
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//viewid
                if (!reader.IsDBNull(2)) rgb.intC3 = reader.GetInt32(2);//num
                if (!reader.IsDBNull(3)) rgb.intC4 = reader.GetInt32(3);//mandatory
                 object[] objArr = { rgb.strC1,  rgb.intC2, rgb.strC3 , rgb.intC4};
                result.Tables["Tabla"].Rows.Add(objArr);
            }

        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        conn.Close();
        return result;
        
    }

    public static int nextPhoto()
    {
        int result = 0;


        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "select top 1 photo_id from [PHOTO] order by 1 desc";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "order");
            dt = ds.Tables["order"];
            conn.Close();
            if (dt.Rows.Count == 0)
            {
                result = 1;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila[0].ToString()) + 1;
                }
            }
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = -1;

        }
        return result;
    }

    public static int addPhotorestriccion(int orden, int view, string comments, string path)
    {// inserta los datos de una foto a la BD
        int result = 0;
        try
        {
            SqlConnection conn = null;
            conn = DB.OpenConnection();
            string now = DateTime.Now.ToString();
            int nextphoto= nextPhoto();
            if (nextphoto==1){
                nextphoto=100;
            }

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "select photo_id from PHOTO where view_id="+view+" and order_id="+orden;
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "photo");
            dt = ds.Tables["photo"];
            if (dt.Rows.Count == 0)
            {
                string q = "insert into PHOTO   (photo_id, view_id, order_id, comments, path) values (" + nextphoto + ", " + view + ", " + orden + " ,'" + comments + "', '" + path + "')";
                SqlCommand insert = new SqlCommand(q, conn);
                insert.ExecuteNonQuery();
                conn.Close();
                result = 1;
            }
            else
            {
                result=0;
            }
        } catch (Exception e)
        {
            result = 0;
            Utility.writeError(e.StackTrace);
        }
        return result;
    }

    public static int addPhoto(int orden, int view, string comments, string path)
    {// inserta los datos de una foto a la BD
        int result = 0;
        try
        {
            SqlConnection conn = null;
            conn = DB.OpenConnection();
            string now = DateTime.Now.ToString();
            int nextphoto = nextPhoto();
            if (nextphoto == 1)
            {
                nextphoto = 100;
            }

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
        

                string q = "insert into PHOTO   (photo_id, view_id, order_id, comments, path) values (" + nextphoto + ", " + view + ", " + orden + " ,'" + comments + "', '" + path + "')";
                SqlCommand insert = new SqlCommand(q, conn);
                insert.ExecuteNonQuery();
                conn.Close();
                result = 1;
           
        }
        catch (Exception e)
        {
            result = 0;
            Utility.writeError(e.StackTrace);
        }
        return result;
    }
    public static int moveImage(string p)
    {
        int result=0;
        try
        {
           
            string sourceFile = HttpContext.Current.Server.MapPath("~\\temp\\"+p);
            string destinationFile = HttpContext.Current.Server.MapPath("~\\data\\photos\\" + p);
            System.IO.File.Move(sourceFile, destinationFile);
            result = 1;
        }
        catch (Exception ex)
        {
            HttpContext.Current.Response.Write("<" + "script language='javascript'>");
            HttpContext.Current.Response.Write("alert('Image not Moved .');");
            HttpContext.Current.Response.Write("<" + "/script>");
           
            
        }
        return result;
    }

    public static string getStateInitial(string namestate)
    {

        string result = "";


        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "select initial from state where name = '" + namestate + "'";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "state");
            dt = ds.Tables["state"];
            if (dt.Rows.Count == 0)
            {
                result = "";
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = (fila[0].ToString());
                }
            }
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = "";

        }
        return result;

    }

    public static int getViewId(string nameView)
    {
        int result = 0;
        try
        {
            SqlConnection conn = null;
            conn = OpenConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter sqladapt;

            string strsql = "SELECT view_id  FROM   [VIEW]  WHERE     (UPPER(name) = '" + nameView.ToUpper() + "')";
            sqladapt = new SqlDataAdapter(strsql, conn);
            sqladapt.Fill(ds, "CAMPOS");
            dt = ds.Tables["CAMPOS"];
            conn.Close();
            if (dt.Rows.Count == 0)
            {
                result = 0;
            }
            else
            {
                foreach (DataRow fila in dt.Rows)
                {
                    result = int.Parse(fila[0].ToString());
                }
            }
        }
        catch (Exception e)
        {

            Utility.writeError(e.StackTrace);
            result = 0;

        }



        return result;
    }



    public static DataSet getPhotosOrden(int orden)
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("photo_id");
        result.Tables["Tabla"].Columns.Add("view_id");
        result.Tables["Tabla"].Columns.Add("num");
        result.Tables["Tabla"].Columns.Add("path");
        result.Tables["Tabla"].Columns.Add("comments");
      


        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT PHOTO.photo_id, PHOTO.view_id, PRODUCT_VIEW.num, PHOTO.path, PHOTO.comments FROM  PHOTO INNER JOIN [VIEW] ON PHOTO.view_id = [VIEW].view_id INNER JOIN PRODUCT_VIEW ON [VIEW].view_id = PRODUCT_VIEW.view_id INNER JOIN [ORDER] ON PHOTO.order_id = [ORDER].order_id INNER JOIN PRODUCT ON PRODUCT_VIEW.product_id = PRODUCT.product_id AND [ORDER].product_id = PRODUCT.product_id WHERE        ([ORDER].order_id = "+orden +") ORDER BY PRODUCT_VIEW.num";

            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0);//photo_id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//view_id
                if (!reader.IsDBNull(2)) rgb.intC3 = reader.GetInt32(2);//num
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//path
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(4);//comments
                object[] objArr = { rgb.intC1, rgb.intC2, rgb.intC3,  rgb.strC2,   rgb.strC3  };
                result.Tables["Tabla"].Rows.Add(objArr);
            }

        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return result;
    }

    public static int savePhotosUploadby(int order, int campoid,string uploadby)
    {//Guarda la infomraicon de Updloadby de las photos 
        int result = 0;
        SqlDataReader reader = null;
        try
        {
            SqlConnection conn = null;
            conn = DB.OpenConnection();
            SqlConnection conninsert = DB.OpenConnection();
            string now = DateTime.Now.ToString();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
         
            
                string verify = "select top (1) id, value from ORDER_PROCESS where order_id="+order+" and campo_id="+campoid+" order by id desc";
                   SqlCommand select = new SqlCommand(verify, conn);
                   reader = select.ExecuteReader();
                   if (reader.HasRows)
                   {
                        string valor = "";
                        while (reader.Read())
                        {
                            if (!reader.IsDBNull(0))
                            {
                                
                                valor = reader.GetString(1);//value

                                if (!valor.ToUpper().Equals(uploadby))
                                {

                                    string q = "insert into [ORDER_PROCESS]   (order_id, campo_id,  value, date) values (" + order + ", " + campoid + ", '" + uploadby + "' ,'" + DateTime.Now + "')";
                                    SqlCommand insert = new SqlCommand(q, conninsert);
                                    insert.ExecuteNonQuery();
                                    result = 1;


                                }
                            }
                        }
                   }else{
                        string qq = "insert into [ORDER_PROCESS]   (order_id, campo_id,  value, date) values (" + order + ", " + campoid + ", '" + uploadby + "' ,'" + DateTime.Now + "')";
                       SqlCommand insert2 = new SqlCommand(qq, conninsert);
                       insert2.ExecuteNonQuery();
                       conninsert.Close();
                       result = 1;
                   
                   }
                 
                       
                   } catch (Exception e)
                {
                    result = 0;
                    Utility.writeError(e.StackTrace);
                }

        
        return result;
        
    }

    public static int saveValueCampoComboBox(int orden, int campoid, string value)
    { //Guarda la informacion 
        int result = 0;
        SqlDataReader reader = null;
        SqlConnection conn2;
        try
        {
            SqlConnection conn = null;
            conn = DB.OpenConnection();
            conn2 = DB.OpenConnection();
            string now = DateTime.Now.ToString();
            

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

                 string date = DateTime.Now.ToString();
                 string verify = "select id, value from ORDER_PROCESS where order_id=" + orden + " and campo_id=" + campoid;
                SqlCommand select = new SqlCommand(verify, conn);
                reader = select.ExecuteReader();
                conn.Close();
                if (reader.HasRows)
                {
                    string valor = "";
                    while(reader.Read()){
                        if (!reader.IsDBNull(1))
                        {
                   
                          valor = reader.GetString(1);//value
                          if (!valor.ToUpper().Equals(value))
                        {
                            // string q = "uptade ORDER_PROCESS set value='" + value + "' where order_id=" + orden + " and campo_id=" + campoid;\
                            string q = "insert into [ORDER_PROCESS]   (order_id, campo_id,  value, date) values (" + orden + ", " + campoid + ", '" + value + "' ,'" + date + "')";
                            SqlCommand insert = new SqlCommand(q, conn2);
                            insert.ExecuteNonQuery();
                            conn2.Close();
                            result = 1;
                        }
                    }
                }
                }
                else
                {
                    string q = "insert into [ORDER_PROCESS]   (order_id, campo_id,  value, date) values (" + orden + ", " + campoid + ", '" + value + "' ,'" + date + "')";
                    SqlCommand insert = new SqlCommand(q, conn2);
                    insert.ExecuteNonQuery();
                    conn2.Close();
                    result = 1;

                }
                
        }
        
        catch (Exception e)
        {
            result = 0;
            Utility.writeError(e.StackTrace);
        }


        return result;
    
    }

    public static int saveValueCampo(int order, int campoid, string value)
    {//Guarda la infomraicon de Updloadby de las photos 
        int result = 0;
        SqlDataReader reader = null;
        SqlConnection conn2;
        try
        {
            SqlConnection conn = null;
            conn = DB.OpenConnection();
            conn2 = DB.OpenConnection();
            string now = DateTime.Now.ToString();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();


            string verify = "select top(1) id, value from ORDER_PROCESS where order_id=" + order + " and campo_id=" + campoid+ "order by id DESC";
            SqlCommand select = new SqlCommand(verify, conn);
            reader = select.ExecuteReader();
            
            if (reader.HasRows)
            {

                    string valor = "";
                    while(reader.Read())
                    {
                        if (!reader.IsDBNull(1))
                        {
                            valor = reader.GetString(1);//value
                            if (!valor.ToUpper().Equals(value))
                            {
                                string q = "insert into [ORDER_PROCESS]   (order_id, campo_id,  value, date) values (" + order + ", " + campoid + ", '" + value + "' ,'" + DateTime.Now + "')";
                                 SqlCommand insert = new SqlCommand(q, conn2);
                            insert.ExecuteNonQuery();
                            conn2.Close();
                            result = 1;

                            }
                        }
                
                }
            }
            else
            {
                if (!value.ToUpper().Equals(""))
                {
                    string q = "insert into [ORDER_PROCESS]   (order_id, campo_id,  value, date) values (" + order + ", " + campoid + ", '" + value + "' ,'" + DateTime.Now + "')";
                    SqlCommand insert = new SqlCommand(q, conn2);
                    insert.ExecuteNonQuery();
                    conn2.Close();
                    result = 1;

                }

            }



        }  catch (Exception e)
        {
            result = 0;
            Utility.writeError(e.StackTrace);
        }

     
        return result;
    }

    public static int getCountViewProduct(){
        //devuelve conteo de vistas por producto
        int result = 0;
       
        

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT COUNT(PRODUCT_VIEW.view_id) AS Suma FROM [VIEW] INNER JOIN PRODUCT_VIEW ON [VIEW].view_id = PRODUCT_VIEW.view_id WHERE  (PRODUCT_VIEW.product_id = 1) AND (PRODUCT_VIEW.mandatory = 1) GROUP BY PRODUCT_VIEW.product_id, PRODUCT_VIEW.mandatory";

            reader = comm.ExecuteReader();
            
            while (reader.Read())
            {
               
                if (!reader.IsDBNull(0)) result= reader.GetInt32(0);
              
            }

        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return 0;
        }



        conn.Close();
        return result;
    
    }

    public static DataSet GetStatus()
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("status_id");//1
        result.Tables["Tabla"].Columns.Add("name");//2

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT status_id, name FROM status";

            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //status_id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//name
                object[] objArr = { rgb.intC1, rgb.strC1 };
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();
        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return result;

    }


    
}
